# coding=utf-8 
#===============================================================================
# Standard Library
#===============================================================================
import hashlib

#===============================================================================
# 3rd party libraries
#===============================================================================
import sqlalchemy
from sqlalchemy import create_engine , ForeignKey
from sqlalchemy import Column, Integer, String, Text, Boolean, UnicodeText
from sqlalchemy.orm import sessionmaker, relation, backref 
from sqlalchemy.ext.declarative import declarative_base 
from sqlalchemy import UniqueConstraint

#===============================================================================
# Own imports
#===============================================================================
import validate
import config

#===============================================================================
# GLOBALS
#===============================================================================
Base = declarative_base()
engine = create_engine(config.DB_NAME, echo=config.ECHO, convert_unicode=True, 
                       encoding='utf8')
Session = sessionmaker(bind=engine)
s = Session()
metadata = Base.metadata

def session_open():
    Session = sessionmaker(bind=engine)
    return Session()

def commit():
    try:
        s.commit()
        content = 'Saved: '
        valid = True   
    except sqlalchemy.exc.InvalidRequestError, e:
        content = e.args
        valid = False
    except sqlalchemy.exc.IntegrityError, e:
        content =  e.args
        content = 'A record with this name or other unique identifier already exists'
        valid = False
    except sqlalchemy.exc.OperationalError, e:
        content = e.args[0]
        valid = False
    s.close() # needed?
    return valid, content

#L_DO: should only be able to commit if valid

#===============================================================================
# ACCESS
# Class consists of helper functions to simplify accessing data objects with
# simpler method/function calls
#===============================================================================
    
class Access():
    can_delete = False
    '''Determines whether an object can be deleted or merely in-activated'''
    def save(self):
        '''If object has a "name" field, then validate it''' 
        valid = True
        content = self
        if 'name' in self.__dict__.keys():
            valid, content = validate.name(self.name)
        if valid:
            s.add(self)
        #TODO validate that names are text no spaces
        return valid, content
        
    def read(self):
        '''Reads object from db and  returns it. Requires that a "name" column 
        is used as unique identifier (if name is a string) or "id" column if 
        this is an integer.'''  
        if self.id:
            filter_string = '{0}.id="{1}"'.format(self.__table__, self.id)
        else:
            filter_string = '{0}.name="{1}"'.format(self.__table__, self.name)
        try:
            object = s.query(self.__class__).filter(filter_string).one()
        except sqlalchemy.orm.exc.NoResultFound, e:
            object = None
        return object
        #J_DO: Add 'valid content' to 'read' method
        #J_DO: refactor into separate into filter string and sql functions
        
    def read_email(self):
        '''Reads object from db and  returns it using email as key.'''  
        filter_string = '{0}.email="{1}"'.format(self.__table__, self.email)
        try:
            object = s.query(self.__class__).filter(filter_string).one()
        except sqlalchemy.orm.exc.NoResultFound, e:
            object = None
        return object
        #J_DO: Add 'valid content' to 'read' method
   
        
    def all(self):
        return s.query(self.__class__).all()
    
#L_DO: handle deleting a None object    


    def delete(self):
        '''Deletes the current object'''
        #K_TODO: have delete return 'valid content' same as commit       
        Obj = self.__class__()
        if Obj.can_delete == True:
            s.delete(self)
        else:
            raise Exception ('Cannot delete objects of type "{0}"'.format(
                                                self.__class__.__name__))
            
        
    def select(self, selection):
        '''Returns a selection of objects. Examples of filters are:
        "_group.id < 4"
        '''
        return s.query(self.__class__).filter(selection).all()

            
    def exists(self):
        '''Returns true if selection returns one or more records'''
        filter_string = '{0}.name="{1}"'.format(self.__table__, self.name)
        return bool(s.query(self.__class__).filter(filter_string).count() > 0)

    def create(self):
        #TEST: the create function
        if self.exists():
            return False, "A record with this name or other unique identifier already exists"
        else:
            return self.save()
        
    def link(self,obj):
        '''General method for linking one object to another'''
        valid = False
        content = 'Method "add" not implemented for adding {0} to {1}'.format(
                                self.__class__.__name__, obj.__class__.__name__)
        if self.__class__.__name__ == 'User':# Adding a user to a groups
            if obj.__class__.__name__ == 'Group':
                #group = Group(name).read()
                u2g = User2Group(self.id, obj.id)
                valid, content = u2g.save()       
        return valid, content
    
    
    def unlink(self,object):
        '''Deletes the link-table record linking the 'left' object to the right object'''
        if self.__class__.__name__ == 'User' and object.__class__.__name__ == 'Group':
            selection ='Users2Groups.user_id = {0} and Users2Groups.group_id ={1}'.format(self.id,object.id)
            link = s.query(User2Group).filter(selection).one()
            # s.delete(link)
            link.delete()
    
    
            
#            print 40, self.__class__ == '__main__.User' 
#            if type(self) == 'User':
#            pass
#        
#        u2g = User2Group(self.id, group.id)
#        valid, content = u2g.save()
#        if valid:
#            valid, content = commit()
#        return valid, content
        
#===============================================================================
#    
# DATA MODEL 
# 
#===============================================================================


    
class User(Base, Access):
    __module__ = __name__
    __tablename__ = 'users'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(24), unique = True) 
    email = Column('email', String(255), unique=True, nullable=False)
    password_hash = Column('password_hash',String(32), nullable=False)
    active = Column('active',Boolean, default = True)
 
    def __init__(self, identifier=""):
        if validate.not_number(identifier)[0]:
            self.name = identifier
        else:
            self.id = int(identifier)
        self.can_delete = False
        self.active = True
#        self.email
#        self.email = email
        
    def __repr__(self):
        return '<User %s "%s">' % (self.id, self.name) 
    
    def groups(self):
        '''Returns the groups that a user belongs to.'''
        #K_DO: Make sure it's only active records returned
        return s.query(Group).join(User2Group).join(User).filter(User.id == 
                                                                 self.id).all()
    def hash(self, password):
        '''Returns the a hash for storage of name + salt + password'''
        m = hashlib.md5()
        m.update(config.SALT + password)
        #N_DO: add e.g. id * 2 to further distinguish passwords
        return unicode(m.hexdigest())
    #M_DO: validate password strength
    
    def authenticates(self, password):
        print 10, password
        print 20, self.hash(password)
        print 30, self.password_hash
        return self.hash(password) == self.password_hash
        
        
       
class Group(Base, Access):
    __module__ = __name__
    __tablename__ = 'groups'
    id = Column('id', Integer, primary_key=True)
    name = Column('name', String(24), unique = True, nullable=False)
    active = Column('active',Boolean, default = True)
    
    def __init__(self, name=None):
        self.name = name

    def __repr__(self):
        return '<Group %s "%s">' % (self.id, self.name)
    
    def users(self):
        '''Returns the users belonging to the group'''
        #J_DO: Make sure it's only active records returned
        return s.query(User).join(User2Group).join(Group).filter(Group.id == 
                                                                 self.id).all()
    
class User2Group(Base, Access):
    __module__ = __name__
    __tablename__ = 'users2groups'
    __table_args__ = (UniqueConstraint('user_id', 'group_id'), {} ) 

    id = Column('id', Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relation(User, order_by='User.id', backref="users")    
    group_id = Column(Integer, ForeignKey('groups.id'))
    group = relation('Group', order_by='Group.id', backref="group")

    
    def __init__(self, user_id=None, group_id=None):
        self.user_id = user_id
        self.group_id = group_id
        self.can_delete = True

    def __repr__(self):
        return "<User2Group %s %s>" % (self.user_id, self.group_id)

if __name__ == '__main__':
    pass

#    metadata.create_all(engine)
#
#

    



    
#    #Create user xxxxx
#    print 0
#    n = User('aaaaaa')
#    n.email="aaaaaa@test.com"
#    print 5, n, n.email, n.name
#    n.password_hash = n.hash('123123')
#    n.save()
#    print 10, commit()
#    
#    u = User()
#    u.email = 'aaaaaa@test.com'
#    n = u.read_email()
#    print 20,  n
#    print 30,  n.password_hash
#    print 40 , n.authenticates('123123')
    
    
    
  
#    u = User('user1').read()
#    print 8, u.id
#    
#    g = User2Group()
#    print 9, g.select('user_id=5')
#    
#    print 10, u.select('id>1')
#    print 20, u
    from pprint import pprint as p
    from sqlalchemy.orm import join, outerjoin
    from sqlalchemy.sql import union
    
    # pprint.pprint(s.query(User2Group,User).select_from(outerjoin(User, User2Group)).filter(User.id==5).all()) # good
    
    # pprint.pprint(s.query(Group).outerjoin(User2Group).all())
    for x in s.query(Group, User2Group).outerjoin(User2Group).all():
        # print x
        pass
    print 10 * '*'

#    print 20, p(s.query(Group, User2Group).filter(User2Group.user_id == 5).outerjoin(User2Group).all())
#    print 30, p(s.query(User2Group, Group).filter(User2Group.user_id == 5).outerjoin(Group).all())
#    print 40, p(s.query(User2Group).select_from(outerjoin(User2Group, Group)).filter(User2Group.user_id == 5).all())
#    print 50, p(s.query(User2Group, Group).select_from(outerjoin(User2Group, Group)).filter(User2Group.user_id == 5).all())
#    print 60, p(s.query(User2Group, Group).select_from(outerjoin(User2Group, Group)).all())
#     print 70, p(s.query(Group, User2Group).any(User2Group))
#     print 80, s.query(outerjoin(Group,User2Group).select()).all()
    
    # retrieves for ALL users
#    print 10, p(s.query(Group, User2Group).outerjoin(User2Group).all()) # retrieves for ALL users
#    
#    # eliminates groups that user 5 doesn't belong to
#    print 90, p(s.query(Group).outerjoin(User2Group).filter(User2Group.user_id==5).all()) # eliminates groups that user 5 doesn't belong to

#    print 95, s.query(Group).outerjoin(User2Group)
#    print 96, s.query(User2Group).filter(User2Group.user_id == 5).all() #'User2Group.user_id = 5'
#    print 97, s.query(User2Group).filter(User2Group.user_id == 5).outerjoin(Group).all()
#    print session.query(Houses).join(Colonials.rooms, Room.closets)
#    print 98, s.query(User2Group).outerjoin(Group).all()
    
#    print 99, p(s.query(Group, User2Group).select_from(outerjoin(Group, User2Group)).all())
#    print 98, p(s.query(User2Group, Group).select_from(outerjoin(User2Group,Group)).all())

    print 99, p(s.query(Group, User2Group.user_id != None).outerjoin((User2Group,(User2Group.group_id == Group.id) & (User2Group.user_id == 6))).all()) 

    print 100, p(s.query(Group, User2Group).outerjoin((User2Group,(User2Group.group_id == Group.id) & (User2Group.user_id == 6))).all()) 

    # print 100, p(session.query(Group).join(User2Group).filter(User2Group.user_id !=5 ).all())
    
    # session.query(User).select_from(join(User, Address)).filter(Address.email_address=='jack@google.com').all() 
    #.filter(User2Group.user_id == 5).outer_join(Group)
    
    #session.query(User).select_from(join(User, Address)).filter(Address.email_address=='jack@google.com').all() 
    
    #.join(User2Group).all())

# M_DO: DB name and db type as config values
#     
# M_DO: check that a call to Group().all() doesnt fail when no groups exist 